/*
 * Copyright (C) 2008 Google Inc.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */

package rs.in.idemdo.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * Simple notes database access helper class. Defines the basic CRUD operations
 * for the notepad example, and gives the ability to list all notes as well as
 * retrieve or modify a specific note.
 * 
 * This has been improved from the first version of this tutorial through the
 * addition of better error handling and also using returning a Cursor instead
 * of using a collection of inner classes (which is less scalable and not
 * recommended).
 */
public class IdemDoDbAdapter {

	/** Naziv i verzija (?) baze */
	public static final String DATABASE_NAME = "baza_podataka";
	public static int DATABASE_VERSION = 2;

	/** Mesto */
	public static final String DATABASE_TABLE_MESTO = "Mesto";
	public static final String KLJUC_MESTO_ID = "_mesto_id";
	public static final String MESTO_NAZIV = "naziv";
	public static final String MESTO_SKRACENO = "skraceno";

	/** Putovanje */
	public static final String DATABASE_TABLE_PUTOVANJE = "Putovanje";
	public static final String KLJUC_PUTOVANJE_ID = "_putovanje_id";
	public static final String PUTOVANJE_MESTO_OD_ID = "mesto_od";
	public static final String PUTOVANJE_MESTO_DO_ID = "mesto_do";
	public static final String PUTOVANJE_TIP_PREVOZA = "tip_prevoza";
	public static final String PUTOVANJE_CENA = "cena";
	public static final String PUTOVANJE_CELINA = "putovanje_celina";
	public static final String PUTOVANJE_PREVOZNIK = "prevoznik";

	/** Tabela Info, tu cemo da guramo sta nam padne na pamet */
	public static final String DATABASE_TABLE_INFO = "Info_tabela";
	public static final String KLJUC_INFO_ID = "_info_id";
	public static final String INFO_NAZIV_PARAMETRA = "naziv_parametra";
	public static final String INFO_VREDNOST_PARAMETRA_STRING = "vrednost_parametra_string";
	public static final String INFO_VREDNOST_PARAMETRA_INT = "vrednost_parametra_int";

	// NEKE RANDOM KONSTANTE KOJE CE BITI VREDNOSTI NEKIH POLJA
	public static final String INFO_CONST_VERZIJA_LISTE_MESTA = "verzija_liste_mesta";

	public static final String TAG = "IdemDoDbAdapter";
	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	private static final String DATABASE_CREATE_MESTO = "CREATE TABLE Mesto ("
			+ "_mesto_id	INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ "naziv		TEXT NOT NULL," + "skraceno		TEXT NOT NULL);";

	private static final String DATABASE_CREATE_PUTOVANJE = "CREATE TABLE Putovanje ("
			+ "_putovanje_id	INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ "mesto_od			INTEGER NOT NULL,"
			+ "mesto_do			INTEGER NOT NULL,"
			+ "tip_prevoza		INTEGER NOT NULL,"
			+ "cena				REAL,"
			+ "putovanje_celina	INTEGER NOT NULL,"
			+ "prevoznik		TEXT NOT NULL,"
			+ "FOREIGN KEY(mesto_od) REFERENCES Mesto(_id),"
			+ "FOREIGN KEY(mesto_do) REFERENCES Mesto(_id));";

	private static final String DATABASE_CREATE_INFO = "CREATE TABLE Info_tabela ("
			+ "_info_id				INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ "naziv_parametra		TEXT NOT NULL,"
			+ "vrednost_parametra_string	TEXT,"
			+ "vrednost_parametra_int	INTEGER);";

	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		//metoda se poziva samo JEDNOM kada se kreira BAZA
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE_INFO);
			db.execSQL(DATABASE_CREATE_MESTO);
			db.execSQL(DATABASE_CREATE_PUTOVANJE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS Mesto; DROP TABLE IF EXISTS Putovanje;DROP TABLE IF EXISTS Info_tabela;");
			onCreate(db);
		}
	}

	public IdemDoDbAdapter(Context ctx) {
		this.mCtx = ctx;
	}

	public IdemDoDbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}


	// MESTO TABELA
	//
	//
	public long createMesto(String mesto_naziv, String mesto_skraceno) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(MESTO_NAZIV, mesto_naziv);
		initialValues.put(MESTO_SKRACENO, mesto_skraceno);

		return mDb.insert(DATABASE_TABLE_MESTO, null, initialValues);
	}

	public Cursor fetchAllMesta() {

		return mDb.query(DATABASE_TABLE_MESTO, new String[] { KLJUC_MESTO_ID,
				MESTO_NAZIV, MESTO_SKRACENO }, null, null, null, null, null);
	}

	public Cursor fetchMesto(long rowId) throws SQLException {

		Cursor mCursor = mDb.query(true, DATABASE_TABLE_MESTO, new String[] {
				KLJUC_MESTO_ID, MESTO_NAZIV, MESTO_SKRACENO }, KLJUC_MESTO_ID
				+ "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public boolean updateMesto(long rowId, String mesto_naziv,
			String mesto_skraceno) {
		ContentValues args = new ContentValues();
		args.put(MESTO_NAZIV, mesto_naziv);
		args.put(MESTO_SKRACENO, mesto_skraceno);

		return mDb.update(DATABASE_TABLE_MESTO, args, KLJUC_MESTO_ID + "="
				+ rowId, null) > 0;
	}

	public boolean deleteMesto(long rowId) {

		return mDb.delete(DATABASE_TABLE_MESTO, KLJUC_MESTO_ID + "=" + rowId,
				null) > 0;
	}

	public boolean obrisiSveTabeleIKreirajPonovo()
	{
		open();
		mDb.execSQL("DROP TABLE IF EXISTS "+ DATABASE_TABLE_MESTO+";");
		mDb.execSQL(DATABASE_CREATE_MESTO);
		
		mDb.execSQL("DROP TABLE IF EXISTS "+ DATABASE_TABLE_PUTOVANJE+";");
		mDb.execSQL(DATABASE_CREATE_PUTOVANJE);
		
		mDb.execSQL("DROP TABLE IF EXISTS "+ DATABASE_TABLE_INFO+";");
		mDb.execSQL(DATABASE_CREATE_INFO);
		
		close();
		return false;
	}
	
	public boolean obrisiSvaMesta() {
		open();
		mDb.execSQL("DROP TABLE IF EXISTS Mesto;");
		mDb.execSQL(DATABASE_CREATE_MESTO);
		close();
		return false;
	}

	// PUTOVANJE
	//
	//

	public long createPutovanje(long mesto_od_id, long mesto_do_id,
			long tip_prevoza, Double cena, long putovanje_celina,
			String prevoznik) {

		ContentValues initialValues = new ContentValues();
		initialValues.put(PUTOVANJE_MESTO_OD_ID, mesto_od_id);
		initialValues.put(PUTOVANJE_MESTO_DO_ID, mesto_do_id);
		initialValues.put(PUTOVANJE_TIP_PREVOZA, tip_prevoza);
		initialValues.put(PUTOVANJE_CENA, cena);
		initialValues.put(PUTOVANJE_CELINA, putovanje_celina);
		initialValues.put(PUTOVANJE_PREVOZNIK, prevoznik);

		return mDb.insert(DATABASE_TABLE_PUTOVANJE, null, initialValues);
	}

	public Cursor fetchAllPutovanja() {

		return mDb.query(DATABASE_TABLE_PUTOVANJE, new String[] {
				PUTOVANJE_MESTO_OD_ID, PUTOVANJE_MESTO_DO_ID,
				PUTOVANJE_TIP_PREVOZA, PUTOVANJE_CENA, PUTOVANJE_CELINA,
				PUTOVANJE_PREVOZNIK }, null, null, null, null, null);
	}

	public Cursor fetchPutovanje(long rowId) throws SQLException {

		Cursor mCursor = mDb.query(true, DATABASE_TABLE_PUTOVANJE,
				new String[] { PUTOVANJE_MESTO_OD_ID, PUTOVANJE_MESTO_DO_ID,
						PUTOVANJE_TIP_PREVOZA, PUTOVANJE_CENA,
						PUTOVANJE_CELINA, PUTOVANJE_PREVOZNIK },
				KLJUC_PUTOVANJE_ID + "=" + rowId, null, null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public boolean updatePutovanje(long putovanje_id, long mesto_od_id,
			long mesto_do_id, long tip_prevoza, Double cena,
			long putovanje_celina, String prevoznik) {

		ContentValues args = new ContentValues();
		args.put(PUTOVANJE_MESTO_OD_ID, mesto_od_id);
		args.put(PUTOVANJE_MESTO_DO_ID, mesto_do_id);
		args.put(PUTOVANJE_TIP_PREVOZA, tip_prevoza);
		args.put(PUTOVANJE_CENA, cena);
		args.put(PUTOVANJE_CELINA, putovanje_celina);
		args.put(PUTOVANJE_PREVOZNIK, prevoznik);

		return mDb.update(DATABASE_TABLE_PUTOVANJE, args, KLJUC_PUTOVANJE_ID
				+ "=" + putovanje_id, null) > 0;
	}

	public boolean deletePutovanje(long rowId) {

		return mDb.delete(DATABASE_TABLE_PUTOVANJE, KLJUC_PUTOVANJE_ID + "="
				+ rowId, null) > 0;
	}

	// INFO
	//
	//

	public long createInfo(String info_naziv_parametra,
			String info_vrednost_string, int info_vrednost_int) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(INFO_NAZIV_PARAMETRA, info_naziv_parametra);
		initialValues.put(INFO_VREDNOST_PARAMETRA_STRING, info_vrednost_string);
		initialValues.put(INFO_VREDNOST_PARAMETRA_INT, info_vrednost_int);

		return mDb.insert(DATABASE_TABLE_INFO, null, initialValues);
	}

	public Cursor fetchAllInfo() {
		return mDb.query(DATABASE_TABLE_INFO, new String[] { KLJUC_INFO_ID,
				INFO_NAZIV_PARAMETRA, INFO_VREDNOST_PARAMETRA_STRING,
				INFO_VREDNOST_PARAMETRA_INT }, null, null, null, null, null);
	}

	public Cursor fetchInfo(String infoNazivParametra) throws SQLException {

		Cursor cursor = mDb.query(true, DATABASE_TABLE_INFO, new String[] {
				KLJUC_INFO_ID, INFO_NAZIV_PARAMETRA, INFO_VREDNOST_PARAMETRA_STRING,
				INFO_VREDNOST_PARAMETRA_INT }, INFO_NAZIV_PARAMETRA + "='"
				+ infoNazivParametra+"'", null, null, null, null, null);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		return cursor;
	}

	public boolean updateInfoById(long rowId, String info_naziv,
			String info_vrednost_string, int info_vrednost_int) {
		ContentValues args = new ContentValues();

		args.put(INFO_NAZIV_PARAMETRA, info_naziv);
		args.put(INFO_VREDNOST_PARAMETRA_STRING, info_vrednost_string);
		args.put(INFO_VREDNOST_PARAMETRA_INT, info_vrednost_int);

		return mDb.update(DATABASE_TABLE_INFO, args, KLJUC_INFO_ID + "=" + rowId,
				null) > 0;
	}

	public boolean updateInfoByNaziv(String info_naziv,
			String info_vrednost_string, int info_vrednost_int) {
		ContentValues args = new ContentValues();

		args.put(INFO_NAZIV_PARAMETRA, info_naziv);
		args.put(INFO_VREDNOST_PARAMETRA_STRING, info_vrednost_string);
		args.put(INFO_VREDNOST_PARAMETRA_INT, info_vrednost_int);

		return mDb.update(DATABASE_TABLE_INFO, args, INFO_NAZIV_PARAMETRA + "="
				+ info_naziv, null) > 0;
	}

	public boolean deleteInfo(long rowId) {

		return mDb.delete(DATABASE_TABLE_INFO, KLJUC_INFO_ID + "=" + rowId, null) > 0;
	}

	public void setVerzijaMesta(int mVerzija_liste_mesta_server) {
		
		Cursor cursor = mDb.query(true, DATABASE_TABLE_INFO, new String[] {
				KLJUC_INFO_ID, INFO_NAZIV_PARAMETRA, INFO_VREDNOST_PARAMETRA_STRING,
				INFO_VREDNOST_PARAMETRA_INT }, INFO_NAZIV_PARAMETRA + "='"
				+ INFO_CONST_VERZIJA_LISTE_MESTA+"'", null, null, null, null, null);
		
		if (cursor != null) {
			if (cursor.moveToFirst())
			{//postoji taj red, treba ga update
				updateInfoByNaziv(INFO_CONST_VERZIJA_LISTE_MESTA, null, mVerzija_liste_mesta_server);
			}
			else
			{//ne postoji takav red, treba ga kreirati
				createInfo(INFO_CONST_VERZIJA_LISTE_MESTA, null, mVerzija_liste_mesta_server);
			}
		}
		
	}

}
